/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Modelo.DAL;

import Modelo.Horario;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import org.json.JSONArray;
import org.json.JSONObject;

/**
 *
 * @author Rafael
 */
public class Horario_DAO {

    public Horario_DAO() {
    }

    public static boolean insertarHorario(String hi, String mini, String meri, String hf, String mf, String merf) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        Statement s2 = null;
        ResultSet rs = null;
        int cont = 0;

        try {
            s = conexion.session.createStatement();
            s2 = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Insertando Horario! " + se.getMessage());
            return false;
        }
        String query2 = "SELECT * FROM horario;";
        System.out.println(query2);
        System.out.println("\n\tQuery: " + query2 + "\n");

        try {
            rs = s2.executeQuery(query2);

            while (rs.next()) {
                cont++;
            }

            cont++;
            String hora_inicio = hi + ":" + mini + " " + meri;
            System.out.println("\n\tQuery: " + hora_inicio + "\n");
            String hora_fin = hf + ":" + mf + " " + merf;
            System.out.println("\n\tQuery: " + hora_fin + "\n");
            String query = "INSERT INTO horario (id_horario,horaInicio,horaFin)"
                    + " VALUES ('" + cont + "','" + hora_inicio + "','" + hora_fin + "')";
            System.out.println(query);
            System.out.println("\n\tQuery: " + query + "\n");

            s.executeUpdate(query);
            return true;
        } catch (SQLException se) {
            System.out.println("Error Insertando horario!" + se.getMessage());
            return false;
        }
    }

    public static JSONObject consultarHorarios(Horario h) {
        ConexionBD conex = new ConexionBD();
        JSONObject tiposObjeto = new JSONObject();

        try {
            Statement st = (Statement) conex.session.createStatement();
            String query = "SELECT a.id_horario, "
                    + "to_char(a.horainicio,'HH:MI am') horainicio, "
                    + "to_char(a.horafin,'HH:MI am') horafin "
                    + "from horario a ";
            String param = "where (1=1) ";
            if (h.getIdHorario() != null && !h.getIdHorario().equals("")) {
                param += "and a.id_horario = " + h.getIdHorario();
            }
            if (h.getHorainicioS() != null && !h.getHorainicioS().equals("")) {
                param += "and a.horainicio = '" + h.getHorainicioS() + "'";
            }
            if (h.getHorafinS() != null && !h.getHorafinS().equals("")) {
                param += "and a.horafin = '" + h.getHorafinS() + "'";
            }
            String format = " order by a.horainicio, a.horafin ";

            System.out.println(query + param + format);
            ResultSet rs = st.executeQuery(query + param + format);
            int i = 0;
            JSONArray ja = new JSONArray();
            while (rs.next()) {
                JSONObject tipoObjeto = new JSONObject();
                tipoObjeto.put("id", rs.getString(1));
                tipoObjeto.put("inicio", rs.getString(2));
                tipoObjeto.put("fin", rs.getString(3));
                ja.put(i++, tipoObjeto);
            }
            tiposObjeto.put("Horarios", ja);

            query = "select count(*) from horario a ";
            System.out.println(query + param);
            rs = st.executeQuery(query + param);
            if (rs.next()) {
                tiposObjeto.put("tam", rs.getString(1));
            }
        } catch (Exception e) {
            System.out.println("Error Consultando Todos Horarios! " + e.getMessage());
        }
        return tiposObjeto;
    }

    public static boolean comprobarHorario(int rsg) throws SQLException {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;
        boolean existe = false;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Comprobando la Existencia de Horarios!");
        }

        String query = "SELECT * FROM personal_dsi WHERE id_horario = '" + rsg + "';";
        System.out.println(query);
        try {
            rs = s.executeQuery(query);
            if (rs.next()) {
                System.out.print(rs.getString(1));
                existe = true;
            }
        } catch (SQLException se) {
            System.out.println("Error Consultando Horarios DAO!");
        }
        return existe;
    }

    public static boolean elimHorario(int rsg) throws SQLException {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;
        boolean elimina = false;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Comprobando la Existencia de Horario!");
        }


        try {

            String query = "DELETE FROM horario WHERE id_horario = '" + rsg + "';";
            System.out.println(query);
            s.executeUpdate(query);
            elimina = true;
        } catch (SQLException se) {
            System.out.println("Error eliminando Horarios DAO! " + se.getMessage());
        }
        return elimina;
    }

    public static boolean comprobarHorario2(int i, String hi, String hf) throws SQLException {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;
        boolean existe = false;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Comprobando la Existencia de Horarios!");
        }

        String query = "SELECT * FROM horario WHERE horainicio = '" + hi + "'and horafin='" + hf + "' ;";
        System.out.println(query);
        try {
            rs = s.executeQuery(query);
            if (rs.next()) {
                System.out.print("AH AH AH ");
                existe = true;
            }
        } catch (SQLException se) {
            System.out.println("Error Consultando Horarios DAO!");
        }
        return existe;
    }

    public static boolean actualizarHorario(int id, String hi, String mini, String meri, String hf, String mf, String merf) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;
        int cont = 0;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Actualizando Horario! " + se.getMessage());
            return false;
        }


        try {

            String hora_inicio = hi + ":" + mini + " " + meri;
            String hora_fin = hf + ":" + mf + " " + merf;
            String query = "UPDATE horario SET horaInicio='" + hora_inicio + "',"
                    + "horaFin='" + hora_fin + "' "
                    + "WHERE id_horario='" + id + "'; ";

            s.executeUpdate(query);
            return true;
        } catch (SQLException se) {
            System.out.println("Error Insertando horario!" + se.getMessage());
            return false;
        }
    }
}
